package repo

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"github.com/redis/go-redis/v9"
	"github.com/star-find-cloud/star-chat/pkg/database"
	log "github.com/star-find-cloud/star-chat/pkg/logger"
	"github.com/star-find-cloud/star-chat/user/domain"
	"github.com/star-find-cloud/star-chat/utils"
)

type UserRepoImpl struct {
	db    database.Database
	cache *database.Redis
}

func NewUserRepo(db database.Database, cache *database.Redis) *UserRepoImpl {
	return &UserRepoImpl{
		db:    db,
		cache: cache,
	}
}

func (r *UserRepoImpl) Create(ctx context.Context, user *domain.User) (int64, error) {
	sqlStr := "insert into star_chat.public.users(name, nick_name, password, email, country_code, phone, age, sex, avatar, tags, last_ip, status, vip_status, vip_expire_time, created_time) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, null, now());"

	result, err := r.db.GetDB().ExecContext(ctx, sqlStr,
		user.Name,
		user.NickName,
		user.Password,
		user.Email,
		user.CountryCode,
		user.Phone,
		user.Age,
		user.Sex,
		user.Avatar,
		user.Tags,
		user.LastIP,
		user.Status,
		user.VipStatus,
		user.VipExpiredTime,
	)

	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database insert user matedata error: %v", err)
			return 0, err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return 0, err
	}

	userID, err := result.LastInsertId()
	if err != nil {
		log.AppLogger.Errorf("get user's id error, err: %v", err)
	}

	return userID, nil
}

func (r *UserRepoImpl) GetAll(ctx context.Context, id int64) (*domain.User, error) {
	var (
		sqlStr = "select * from star_chat.public.users where id = ?;"
		dbUser = &userDB{}
	)

	var err = r.db.GetDB().GetContext(ctx, dbUser, sqlStr, id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database get user matedata error: %v", err)
			return nil, err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return nil, err
	}

	return &domain.User{
		ID:             dbUser.ID,
		Name:           dbUser.Name,
		NickName:       dbUser.NickName,
		Password:       dbUser.Password,
		Email:          dbUser.Email,
		CountryCode:    dbUser.CountryCode,
		Phone:          dbUser.Phone,
		Age:            dbUser.Age,
		Sex:            dbUser.Sex,
		Avatar:         dbUser.Avatar,
		Tags:           dbUser.Tags,
		Status:         dbUser.Status,
		LastIP:         dbUser.LastIP,
		VipStatus:      dbUser.VipStatus,
		VipExpiredTime: dbUser.VipExpiredTime.Unix(),
		CreateTime:     dbUser.CreateTime.Unix(),
		UpdateTime:     dbUser.UpdateTime.Unix(),
		DeleteTime:     dbUser.UpdateTime.Unix(),
	}, nil

}

func (r *UserRepoImpl) GetByID(ctx context.Context, id int64) (*domain.User, error) {
	var (
		sqlStr = "select id, name, nick_name, email, country_code, phone, age, sex, avatar, tags, last_ip, status, vip_status, vip_expire_time, created_time, updated_time, deleted_time from star_chat.public.users where id = ?;"
		dbUser = &userDB{}
	)

	var err = r.db.GetDB().GetContext(ctx, dbUser, sqlStr, id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database get user matedata error: %v", err)
			return nil, err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return nil, err
	}

	return &domain.User{
		ID:             dbUser.ID,
		Name:           dbUser.Name,
		NickName:       dbUser.NickName,
		Email:          dbUser.Email,
		CountryCode:    dbUser.CountryCode,
		Phone:          dbUser.Phone,
		Sex:            dbUser.Sex,
		Avatar:         dbUser.Avatar,
		Tags:           dbUser.Tags,
		Status:         dbUser.Status,
		LastIP:         dbUser.LastIP,
		VipStatus:      dbUser.VipStatus,
		VipExpiredTime: dbUser.VipExpiredTime.Unix(),
	}, nil
}

func (r *UserRepoImpl) GetByEmail(ctx context.Context, email string) (*domain.User, error) {
	var (
		sqlStr = "select id, name, nick_name , email, country_code, phone, sex, avatar, tags, status, last_ip, vip_status, vip_expire_time from star_chat.public.users where email = ?;"
		dbUser = &userDB{}
	)

	var err = r.db.GetDB().GetContext(ctx, dbUser, sqlStr, email)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database get user matedata error: %v", err)
			return nil, err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return nil, err
	}

	return &domain.User{
		ID:             dbUser.ID,
		Name:           dbUser.Name,
		NickName:       dbUser.NickName,
		Email:          dbUser.Email,
		CountryCode:    dbUser.CountryCode,
		Phone:          dbUser.Phone,
		Sex:            dbUser.Sex,
		Avatar:         dbUser.Avatar,
		Tags:           dbUser.Tags,
		Status:         dbUser.Status,
		LastIP:         dbUser.LastIP,
		VipStatus:      dbUser.VipStatus,
		VipExpiredTime: dbUser.VipExpiredTime.Unix(),
	}, nil
}

func (r *UserRepoImpl) GetByPhone(ctx context.Context, countryCode, phone int64) (*domain.User, error) {
	var (
		sqlStr = "select id, name, nick_name, email, country_code, phone, sex, tags, status, vip_status, vip_expire_time from star_chat.public.users where phone = ? and country_code = ? ;"
		dbUser = &userDB{}
	)

	var err = r.db.GetDB().GetContext(ctx, dbUser, sqlStr, phone, countryCode)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database get user matedata error: %v", err)
			return nil, err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return nil, err
	}

	return &domain.User{
		ID:             dbUser.ID,
		Name:           dbUser.Name,
		NickName:       dbUser.NickName,
		Email:          dbUser.Email,
		CountryCode:    dbUser.CountryCode,
		Phone:          dbUser.Phone,
		Sex:            dbUser.Sex,
		Avatar:         dbUser.Avatar,
		Tags:           dbUser.Tags,
		Status:         dbUser.Status,
		LastIP:         dbUser.LastIP,
		VipStatus:      dbUser.VipStatus,
		VipExpiredTime: dbUser.VipExpiredTime.Unix(),
	}, nil
}

func (r *UserRepoImpl) CheckPasswordByID(ctx context.Context, id int64, password string) (bool, error) {
	var sqlStr = "select password from star_chat.public.users where id=?;"

	var result string
	err := r.db.GetDB().GetContext(ctx, &result, sqlStr, id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database get user matedata error: %v", err)
			return false, err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return false, err
	}
	return password == result, nil
}

func (r *UserRepoImpl) CheckPasswordByPhone(ctx context.Context, countryCode, phone int64, password string) (bool, error) {
	var sqlStr = "select password from star_chat.public.users where country_code=? and phone = ?;"

	var result string
	err := r.db.GetDB().GetContext(ctx, &result, sqlStr, countryCode, phone)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database get user matedata error: %v", err)
			return false, err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return false, err
	}
	return password == result, nil
}

func (r *UserRepoImpl) CheckPasswordByEmail(ctx context.Context, email, password string) (bool, error) {
	var sqlStr = "select password from star_chat.public.users where email = ?;"

	var result string
	err := r.db.GetDB().GetContext(ctx, &result, sqlStr, email)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database get user matedata error: %v", err)
			return false, err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return false, err
	}
	return password == result, nil
}

func (r *UserRepoImpl) CheckStatus(ctx context.Context, id int64) (string, error) {
	sqlStr := "select status from star_chat.public.users where id = ?;"
	var status string

	err := r.db.GetDB().GetContext(ctx, status, sqlStr, id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database get user matedata error: %v", err)
			return "", err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return "", err
	}
	return status, nil
}

func (r *UserRepoImpl) Update(ctx context.Context, user *domain.User) error {
	var sqlStr = "update star_chat.public.users set name=?, nick_name=?, email=?, country_code=?,phone=?,sex=?,tags=?,status=?, last_ip=?, vip_status=?,vip_expire_time=? , updated_time = now() where id = ?;"

	_, err := r.db.GetDB().ExecContext(ctx, sqlStr,
		user.Name,
		user.NickName,
		user.Email,
		user.CountryCode,
		user.Phone,
		user.Sex,
		user.Tags,
		user.Status,
		user.LastIP,
		user.VipStatus,
		user.VipExpiredTime,
		user.ID,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database update user matedata error: %v", err)
			return err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return err
	}
	return nil
}

func (r *UserRepoImpl) UpdatePassword(ctx context.Context, id int64, newPassword string) error {
	var sqlStr = "update star_chat.public.users set password = ? , updated_time = now() where id = ?;"

	_, err := r.db.GetDB().ExecContext(ctx, sqlStr, newPassword, id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database update user matedata error: %v", err)
			return err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return err
	}
	return nil
}

func (r *UserRepoImpl) UpdatePhone(ctx context.Context, id, countryCode, Phone int64) error {
	var sqlStr = "update star_chat.public.users set country_code=?, phone=?, updated_time = now() where id=?;"

	_, err := r.db.GetDB().ExecContext(ctx, sqlStr, countryCode, Phone, id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database update user phone error: %v", err)
			return err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return err
	}
	return nil
}

func (r *UserRepoImpl) UpdateAvatar(ctx context.Context, avatarID, id int64) error {
	var sqlStr = "update star_chat.public.users set avatar=?, updated_time = now() where id = ?;"

	_, err := r.db.GetDB().ExecContext(ctx, sqlStr, avatarID, id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database update user avatar err: %v", err)
			return err
		}
		log.AppLogger.Errorf("an error occurred in the user repo: %v", err)
		return err
	}
	return nil
}

func (r *UserRepoImpl) UpdateStatus(ctx context.Context, id int64, status string) error {
	var sqlStr = "update star_chat.public.users set status = ? where id=?"

	_, err := r.db.GetDB().ExecContext(ctx, sqlStr, status, id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database update user status err: %v", err)
			return err
		}
		log.AppLogger.Errorf("an error occurred in the user repo: %v", err)
		return err
	}
	return nil
}

func (r *UserRepoImpl) Delete(ctx context.Context, id int64) error {
	var sqlStr = "update star_chat.public.users set status = ?, deleted_time = now() where id = ?;"

	_, err := r.db.GetDB().ExecContext(ctx, sqlStr, domain.Deleted, id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database delete user matedata error: %v", err)
			return err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return err
	}
	return err
}

func (r *UserRepoImpl) IsExistsByID(ctx context.Context, id int64) (bool, error) {
	var sqlStr = "select exists(select 1 from star_chat.public.users where id = ?) as value_exists;"
	var exists bool

	err := r.db.GetDB().GetContext(ctx, exists, sqlStr, id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database check id exists error: %v", err)
			return false, err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return false, err
	}
	return exists, nil
}

func (r *UserRepoImpl) IsExistsByPhone(ctx context.Context, countryCode, phone int64) (bool, error) {
	var sqlStr = "select exists(select 1 from star_chat.public.users where phone = ? and country_code = ?) as value_exists"
	var exists bool

	err := r.db.GetDB().GetContext(ctx, exists, sqlStr, countryCode, phone)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database check phone exists error: %v", err)
			return false, err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return false, err
	}
	return exists, err
}

func (r *UserRepoImpl) IsExistsByEmail(ctx context.Context, email string) (bool, error) {
	var sqlStr = "select exists(select 1 from star_chat.public.users where email=?) as value_exists"
	var exists bool

	err := r.db.GetDB().GetContext(ctx, exists, sqlStr, email)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			log.SQLLogger.Errorf("Database check email exists error: %v", err)
			return false, err
		}
		log.AppLogger.Errorf("an error occurred in the user repo, err: %v", err)
		return false, err
	}
	return exists, err
}

func (r *UserRepoImpl) CheckEmailVerificationCode(ctx context.Context, email string, verificationCode string) (bool, error) {
	// 从Redis缓存中获取存储的验证码
	storedCode, err := r.cache.GetCache().Get(ctx, email).Result()
	if err != nil {
		if errors.Is(err, redis.Nil) {
			log.CacheLogger.Warnf("email:%s err: %v", email, err)
			return false, err
		}
		log.AppLogger.Errorf("获取验证码失败: %v", err)
		return false, fmt.Errorf("获取验证码失败: %w", err)
	}

	// 比较验证码是否匹配
	isValid, err := utils.ValidateCode(verificationCode, storedCode)
	if err != nil {
		log.AppLogger.Errorln(err)
		return false, err
	}

	// 如果验证成功，删除验证码防止重复使用
	if isValid {
		_, err = r.cache.GetCache().Del(ctx, email).Result()
		if err != nil {
			log.AppLogger.Warnf("删除已使用的验证码失败: %v", err)
		}
	}

	return isValid, nil
}
